from django.db import connection
from decimal import Decimal

# def get_douban_limit_n(n):
#      with connection.cursor() as cursor:
#         cursor.execute('select * from douban_movie_info as douban, en_movie_info as en where douban.en_id=en.id limit %s', [n])
#         return cursor.fetchall()
#


def get_douban_limit_n(n):
    with connection.cursor() as cursor:
        cursor.execute('select douban.title, douban.score, douban.url, douban.cover_url, en.boxoffice from douban_movie_info as douban, en_movie_info as en where douban.en_id=en.id limit %s', [n])
        columns = [col[0] for col in cursor.description]
        res = cursor.fetchall()
        res = convert_decimal_to_float(res)
        print(res)
        return [dict(zip(columns, row)) for row in res] # 返回以字段名为键的字典列表




# 查询分数为9的

def get_douban_limit_n_by_score(n):
    with connection.cursor() as cursor:
        cursor.execute('select douban.title, douban.score, douban.url, douban.cover_url, en.boxoffice from douban_movie_info as douban, en_movie_info as en where douban.en_id=en.id and douban.score = %s', [n])
        columns = [col[0] for col in cursor.description]
        res = cursor.fetchall()
        res = convert_decimal_to_float(res)
        print(res)
        return [dict(zip(columns, row)) for row in res] # 返回以字段名为键的字典列表

def get_douban_limit_n_by_score_big(n):
    with connection.cursor() as cursor:
        cursor.execute('select douban.title, douban.score, douban.url, douban.cover_url, en.boxoffice from douban_movie_info as douban, en_movie_info as en where douban.en_id=en.id and douban.score > %s', [n])
        columns = [col[0] for col in cursor.description]
        res = cursor.fetchall()
        res = convert_decimal_to_float(res)
        print(res)
        return [dict(zip(columns, row)) for row in res] # 返回以字段名为键的字典列表




# def get_douban_limit_n_by_title(n):
#     with connection.cursor() as cursor:
#         cursor.execute("select douban.title, douban.score, douban.url, douban.cover_url, en.boxoffice from douban_movie_info as douban, en_movie_info as en where douban.en_id=en.id and douban.title like '%%%s%%'", [n])
#         columns = [col[0] for col in cursor.description]
#         res = cursor.fetchall()
#         res = convert_decimal_to_float(res)
#         print(res)
#         return [dict(zip(columns, row)) for row in res] # 返回以字段名为键的字典列表

#查询电影名叫西游记开头的
def get_douban_limit_n_by_title(n):
    with connection.cursor() as cursor:
        # 使用 % 来表示百分号，并且使用 % 来表示字符串匹配
        query = "SELECT douban.title, douban.score, douban.url, douban.cover_url, en.boxoffice " \
                "FROM douban_movie_info AS douban, en_movie_info AS en " \
                "WHERE douban.en_id = en.id AND douban.title LIKE %s"

        # 使用 % 来表示百分号，并且使用 % 来表示字符串匹配
        search_pattern = f"%{n}%"

        # 执行查询，传递参数
        cursor.execute(query, (search_pattern,))

        # 获取列名
        columns = [col[0] for col in cursor.description]

        # 获取查询结果
        res = cursor.fetchall()

        # 转换 Decimal 类型为 float（假设 convert_decimal_to_float 是一个已定义的函数）
        res = convert_decimal_to_float(res)

        # 打印结果
        print(res)

        # 返回以字段名为键的字典列表
        return [dict(zip(columns, row)) for row in res]


def get_douban_limit_n_by_rank(n):
    with connection.cursor() as cursor:
        cursor.execute('select  en.movie_name from en_movie_info as en where en.rank = %s', [n])
        columns = [col[0] for col in cursor.description]
        res = cursor.fetchall()
        res = convert_decimal_to_float(res)
        print(res)
        return [dict(zip(columns, row)) for row in res] # 返回以字段名为键的字典列表







def insert_type(name, code):
    with connection.cursor() as cursor:
        cursor.execute('insert into type_info (code, name) values (%s, %s)', [code, name])
        cursor.execute('commit')


def insert_movie(code, movie_name, avg_audience_count, release_time, avg_boxoffice, boxoffice, rank):
    print(code, movie_name, avg_audience_count, release_time, avg_boxoffice, boxoffice, rank)
    with connection.cursor() as cursor:
        cursor.execute(
                """
                INSERT INTO EN_MOVIE_INFO (CODE, MOVIE_NAME, AVG_AUDIENCE_COUNT, RELEASE_TIME, AVG_BOXOFFICE, BOXOFFICE, RANK)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                """,
                [code, movie_name, avg_audience_count, release_time, avg_boxoffice, boxoffice, rank]
        )

            # 提交事务
        cursor.execute('COMMIT')

def del_movie(code):
    with connection.cursor() as cursor:
        cursor.execute('DELETE FROM EN_MOVIE_INFO WHERE CODE = %s ',[code])
        cursor.execute('COMMIT')


def updta_movies(code,newname):
    with connection.cursor() as cursor:
        cursor.execute("""
        UPDATE EN_MOVIE_INFO
        SET MOVIE_NAME = %s
        WHERE CODE = %s;
        """,[newname,code])
        cursor.execute('COMMIT')



def convert_decimal_to_float(obj):
    print(type(obj))
    if isinstance(obj, dict):
        return {k: convert_decimal_to_float(v) for k, v in obj.items()}
    elif isinstance(obj, list):
        return [convert_decimal_to_float(item) for item in obj]
    elif isinstance(obj, tuple):
        return [convert_decimal_to_float(item) for item in obj]
    elif isinstance(obj, Decimal):
        return float(obj)  # 或者 str(obj)
    return obj